﻿IF EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'HorasLaboradasGet')
	BEGIN
		DROP  Procedure  HorasLaboradasGet
	END
GO

CREATE PROCEDURE HorasLaboradasGet 	
	(	
		@empresaId		AS INT,		
		@operarioLista	AS VARCHAR(4000) = NULL,
		@fechaInicial	AS DATETIME = NULL,		
		@fechaFinal		AS DATETIME = NULL		
	)	
AS
BEGIN	
	SET NOCOUNT ON
	
	-- Lista de tablas a ser devueltas a la aplicacion para cargar la hoja de registro	
	SELECT 0 AS TableId, 'Catalog'			AS TableName	UNION
	SELECT 1 AS TableId, 'Horas'			AS TableName	

	DECLARE @operarios TABLE (Id INT)
	DECLARE @result TABLE 
			(NumeroOT		VARCHAR(30),
			 Operario		VARCHAR(50),
			 HorasR			DECIMAL(10,2),			 
			 HorasE			DECIMAL(10,2),
			 HorasD			DECIMAL(10,2),
			 CostoHorasR	DECIMAL(10,2),
			 CostoHorasE	DECIMAL(10,2),
			 CostoHorasD	DECIMAL(10,2))

	DECLARE @del CHAR(1);
	SET @del=',';
	
	SET @operarioLista = @operarioLista + @del;
	
	DECLARE @value varchar(20);
	WHILE charindex(@del,@operarioLista,0) > 0
	BEGIN
		SELECT	@value  = rtrim(ltrim(substring(@operarioLista,1,charindex(@del,@operarioLista,0)-1))),
				@operarioLista = rtrim(ltrim(substring(@operarioLista,charindex(@del,@operarioLista,0)+1,len(@operarioLista))))
		IF NOT EXISTS(SELECT @value FROM @operarios WHERE id=@value)
		BEGIN
			INSERT INTO @operarios(Id) VALUES(@value)
		END
	END	

	INSERT INTO @result
		SELECT OT.NumeroOT, O.Nombre AS Operario, SUM(HorasRegular) HorasR, SUM(HorasExtra) HorasE, SUM(HorasDoble) HorasD,
				SUM(HorasRegular*CostoOperarioxHora) CostoHorasR, 
				SUM(HorasExtra*CostoOperarioxHora*1.5) CostoHorasE, 
				SUM(HorasDoble*CostoOperarioxHora*2) CostoHorasD 			   
		FROM Lit_OTDetalle OTD
		INNER JOIN Lit_OTEntrega OTE ON OTD.OTEntregaId = OTE.Id
		INNER JOIN Lit_OT OT ON OTE.OTId = OT.Id		
		INNER JOIN Lit_Operario O ON OTD.OperarioId = O.Id	
		INNER JOIN @operarios L ON L.Id = O.Id
		WHERE OT.EmpresaId = @empresaId	  
		  AND Fecha Between ISNULL(@fechaInicial,Fecha) AND ISNULL(@fechaFinal,Fecha)	  
		GROUP BY OT.NumeroOT, O.Nombre
		UNION
		SELECT	OT.NumeroOT, O.Nombre AS Operario, SUM(HorasRegular) HorasR, SUM(HorasExtra) HorasE, SUM(HorasDoble) HorasD,				
				SUM(HorasRegular*CostoAyudantexHora) CostoHorasR, 
				SUM(HorasExtra*CostoAyudantexHora*1.5) CostoHorasE, 
				SUM(HorasDoble*CostoAyudantexHora*2) CostoHorasD
		FROM Lit_OTDetalle OTD
		INNER JOIN Lit_OTEntrega OTE ON OTD.OTEntregaId = OTE.Id
		INNER JOIN Lit_OT OT ON OTE.OTId = OT.Id		
		INNER JOIN Lit_Operario O ON OTD.AyudanteId = O.Id	
		INNER JOIN @operarios L ON L.Id = O.Id
		WHERE OT.EmpresaId = @empresaId	  
		  AND Fecha Between ISNULL(@fechaInicial,Fecha) AND ISNULL(@fechaFinal,Fecha)	  
		GROUP BY OT.NumeroOT, O.Nombre
	
	SELECT E.Nombre AS EmpresaNombre, @fechaInicial AS FechaInicial, @fechaFinal AS FechaFinal, NumeroOT, Operario, 
		   ISNULL(SUM(HorasR),0) HorasR, ISNULL(SUM(HorasE),0) HorasE, ISNULL(SUM(HorasD),0) HorasD, 
		   ISNULL(SUM(CostoHorasR),0) CostoHorasR, ISNULL(SUM(CostoHorasE),0) CostoHorasE, ISNULL(SUM(CostoHorasD),0) CostoHorasD, 
		   ISNULL(SUM(HorasR),0) + ISNULL(SUM(HorasE),0) + ISNULL(SUM(HorasD),0) AS HorasTotal,
		   ISNULL(SUM(CostoHorasR),0) + ISNULL(SUM(CostoHorasE),0) + ISNULL(SUM(CostoHorasD),0) AS CostoHorasTotal
	FROM @result
	INNER JOIN LD_General..Empresas E ON (E.Id = @empresaId)
	GROUP BY E.Nombre, NumeroOT, Operario	

	SET NOCOUNT OFF
END
GO
